//
//  DataBaseHandle.m
//  UI18_SQLite数据库
//
//  Created by dlios on 15-8-5.
//  Copyright (c) 2015年 lanou3g.com 蓝鸥科技. All rights reserved.
//

#import "DataBaseHandle.h"
#import <sqlite3.h>

@implementation DataBaseHandle

+ (instancetype)shareDataBaseHandle
{
    /* 
     *static 修饰词作用，1.在静态去开空间，程序退出时，内存才释放，2.只初始化一次
     */
    static DataBaseHandle *dataBaseHandle = nil;
    if (dataBaseHandle == nil) {
        dataBaseHandle = [[DataBaseHandle alloc] init];
        
    }
    
    return dataBaseHandle;
}

#pragma mark - 数据库相关操作
/* 创建一个数据库对象 */
static sqlite3 *db;

/* 打开数据库 */
- (BOOL)open
{
    NSString *docPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
    NSLog(@"%@",docPath);
    /* 数据文件路径 */
    NSString *sqlPath = [docPath stringByAppendingPathComponent:@"like.sqlite"];
    
    /* 如果数据库已经打开，直接返回，不运行下面代码 */
    
    if (db != nil) {
        NSLog(@"数据库已经打开");
        return YES;
    }
    else
    {
    /* 核心API:sqlite3_open 函数 */
    int result = sqlite3_open(sqlPath.UTF8String, &db);
    
    /* 根据函数返回值，判断操作是否正确 */
    
    if (result == SQLITE_OK) {
        NSLog(@"数据库打开成功");
        return YES;
    }
    else
        NSLog(@"数据库打开失败");
        return NO;
    
    }
   
}

/* 创建表单 */
- (BOOL)createTable
{
    /* 创建SQL语句 */
    NSString *createSQL = @"CREATE TABLE IF NOT EXISTS collectionView(title text PRIMARY KEY, url text, type text)";
    
    
    /* 核心API:sqlite3_exec 函数 */
    int result = sqlite3_exec(db, createSQL.UTF8String, NULL, NULL, nil);
    if (SQLITE_OK == result) {
        NSLog(@"创建表格成功");
        return YES;
    }
    else
    {
        NSLog(@"创建表格失败");
        return NO;
    }
}



/* 关闭数据库 */
- (BOOL)close
{
    int result = sqlite3_close(db);
    if (SQLITE_OK == result) {
        /* 将db指针指向空指针 */
        db = nil;
        NSLog(@"关闭数据库成功");
        return YES;
    }
    else
    {
        NSLog(@"关闭数据库失败");
        return NO;
    }
}

/* 输入数据 */
- (BOOL)insertView:(NSDictionary *)dic
{
    if ([self open])
    {
        if ([self createTable])
        {

            /* 创建SQL语句 */
            NSString *insertSQL = [NSString stringWithFormat:@"INSERT INTO collectionView(title,url,type) VALUES ('%@','%@','%@')",[dic objectForKey:@"title"],[dic objectForKey:@"url"],[dic objectForKey:@"type"]];
            
            /* 核心API:sqlite3_exec */
            int result = sqlite3_exec(db, insertSQL.UTF8String, NULL, NULL, nil);
            if (SQLITE_OK == result)
            {
                NSLog(@"插入数据成功");
                if ([self close])
                {
                    return YES;
                }
                else
                    return NO;
                
            }
            else
            {
                NSLog(@"插入数据失败");
                return NO;
            }
        }
        else
            return NO;
    }
    else
        return NO;
    
    
    
    
}

/* 删除数据 */
- (BOOL)deleteView:(NSString *)title
{
    
    if ([self open])
    {
        
        NSString *deleteSQL = [NSString stringWithFormat:@"DELETE FROM collectionView WHERE title = '%@'",title];
        
        /* 核心API:sqlite3_exec */
        int result = sqlite3_exec(db, deleteSQL.UTF8String, NULL, NULL, nil);
        
        if (SQLITE_OK == result) {
            NSLog(@"删除成功");
            if ([self close])
            {
                return YES;
            }
            else
                return NO;
        }
        else
        {
            NSLog(@"删除失败");
            return NO;
        }
        
    }
    else
        return NO;
}
/* 查询数据 */
- (BOOL)selectViewWithTitle:(NSString *)title;
{
    
    if ([self open])
    {
        
        /* 创建SQL查询语句 */
        NSString *selectSQL = [NSString stringWithFormat:@"SELECT * FROM collectionView WHERE title = '%@'",title];
        
        /* 核心API： */
        /* 准备好的SQL语句对象 */
        sqlite3_stmt *stmt = nil;
        
        /* 编译 */
        int result = sqlite3_prepare_v2(db, selectSQL.UTF8String, -1, &stmt, nil);
        
        if (SQLITE_OK == result)
        {
            NSLog(@"查询成功");
            
           if (SQLITE_ROW == sqlite3_step(stmt) )
            {
                
                    sqlite3_finalize(stmt);
                    if ([self close])
                    {
                        return YES;
                    }
                    else
                        return NO;
            }
           else
           {
               sqlite3_finalize(stmt);
               [self close];
               return NO;
           }
 
            
        
        }
        else
        {
            NSLog(@"查询失败");
            sqlite3_finalize(stmt);
            return NO;
        }
    }
    else
        return NO;
}


- (NSArray *)selectViewWithType:(NSString *)type
{
    NSMutableArray *arr = [NSMutableArray array];
    
    if ([self open])
    {
        
        /* 创建SQL查询语句 */
        NSString *selectSQL = [NSString stringWithFormat:@"SELECT * FROM collectionView WHERE type = '%@'",type];
        
        /* 核心API： */
        /* 准备好的SQL语句对象 */
        sqlite3_stmt *stmt = nil;
        
        /* 编译 */
        int result = sqlite3_prepare_v2(db, selectSQL.UTF8String, -1, &stmt, nil);
        
        if (SQLITE_OK == result)
        {
            NSLog(@"查询成功");

                while (SQLITE_ROW == sqlite3_step(stmt) ) {
                    
                    /* 当需要对查询之后的结果进行处理操作是，使用sqlite3_column_ */
                    const unsigned char *title =  sqlite3_column_text(stmt, 0);/* 取出name */
                    const unsigned char *url = sqlite3_column_text(stmt, 1);/* 取出sex */
                    
                
                    NSDictionary *dic = [NSDictionary dictionaryWithObjects:
           @[[NSString stringWithUTF8String:(const char *)title],[NSString stringWithUTF8String:(const char *)url]] forKeys:@[@"title",@"url"]];
                    
                    [arr addObject:dic];
                    
                    
                }
                sqlite3_finalize(stmt);
                if ([self close])
                {
                    return arr;
                }
                else
                    return arr;
            }

    else
    {
        NSLog(@"查询失败");
        sqlite3_finalize(stmt);
        [self close];
        return arr;
    }
    }
    else
        return arr;
}


@end
